package com.cinema.dao.imp;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.cinema.Bean.Hall;
import com.cinema.Bean.Movie;
import com.cinema.Bean.MovieSession;
import com.cinema.Bean.MsgStatistical;
import com.cinema.dao.BaseDao;
import com.cinema.dao.IMovieSessionDao;
import com.mysql.jdbc.PreparedStatement;

/**
 * 用户表的mysql数据库实现类
 * **/

public class MovieSessionDaoMysql extends BaseDao implements IMovieSessionDao {
	public int insert(MovieSession a) {
		String sql = "insert into session values(?,?,?,?,?)";
		String[] args = { a.getSessionId(),a.getHallId(), a.getMovieId(),a.getTime(),a.getPrice()+""};
		return change(sql, args);
	}

	public int delete(MovieSession a) {
		String sql = "delete from session where sessionId=?";
		String[] args = { a.getSessionId() };
		return change(sql, args);
	}

	public int update(MovieSession a) {
		String sql = "update session set hallId=?, movieId=?,time=?,price=? where sessionId=?";
		String[] args = { a.getHallId(), a.getMovieId(),a.getTime(),a.getPrice()+"",a.getSessionId()};
		return change(sql, args);
	}

	/**
	 * 根据Id进行查询
	 */
	public MovieSession SelectById(String id) {
		// TODO Auto-generated method stub
		MovieSession a = null;
		String sql = "select * from session where sessionId=?";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				a = new MovieSession();
				a.setSessionId(rs.getString("sessionId"));
				a.setHallId(rs.getString("hallId"));
				a.setMovieId(rs.getString("movieId"));
				a.setTime(rs.getString("time"));
				a.setPrice(rs.getDouble("price"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}
	
	/**
	 * 根据传入条件进行查询,获取场次 以及场厅名
	 */
	public List<Map<String,Object>> selectByWhere(String where) {
		Map map;
		// TODO Auto-generated method stub
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		MovieSession a = null;
		Hall h = null;
		String sql = "select * from session s,hall h where s.hallId = h.hallId "; // where字句
		if(where!=null && !where.trim().equals("")) {
			sql+="and "+where +"order by time ";
		}
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con
					.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				map = new HashMap();
				a = new MovieSession();
				h = new Hall();
				a.setSessionId(rs.getString("sessionId"));
				a.setHallId(rs.getString("hallId"));
				a.setMovieId(rs.getString("movieId"));
				a.setTime(rs.getString("time"));
				a.setPrice(rs.getDouble("price"));
				a.setDate(rs.getTimestamp("time"));
				h.setHallId(rs.getString("hallId"));
				h.setHallName(rs.getString("hallName"));
				map.put("movieSession",a);
				map.put("hall",h);
				list.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 获取所有
	 */
	public List<MovieSession> selectAll() {
		// TODO Auto-generated method stub
		List<MovieSession> list = new ArrayList<MovieSession>();
		MovieSession a = null;
		String sql = "select * from session";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con
					.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				a = new MovieSession();
				a.setSessionId(rs.getString("sessionId"));
				a.setHallId(rs.getString("hallId"));
				a.setMovieId(rs.getString("movieId"));
				a.setTime(rs.getString("time"));
				a.setPrice(rs.getDouble("price"));
				list.add(a);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 根据关键词进行查询
	 */
	public List<MovieSession> Select(String keyword) {
		// TODO Auto-generated method stub
		List<MovieSession> list = new ArrayList<MovieSession>();
		MovieSession a = null;
		String sql = "select * from session where sessionId like '%" + keyword
		+ "%' or hallId like '%" + keyword
		+ "%' or movieId like '%" + keyword + "%'";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con
					.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				a = new MovieSession();
				a.setSessionId(rs.getString("sessionId"));
				a.setHallId(rs.getString("hallId"));
				a.setMovieId(rs.getString("movieId"));
				a.setTime(rs.getString("time"));
				a.setPrice(rs.getDouble("price"));
				
				list.add(a);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 获取所有场次
	 */
	public List<MsgStatistical> selectByStatWhere() {
		MsgStatistical m = null;
		List<MsgStatistical> list = new ArrayList<MsgStatistical>();
		String sql = "select \r\n" + 
				"	COUNT(s.sessionId) AS \"countSession\",\r\n" + 
				"	date_format(s.time, '%Y-%m-%d' ) as \"time\"\r\n" + 
				"from session as s\r\n" + 
				"GROUP BY date_format(s.time, '%Y-%m-%d' )\r\n" + 
				"ORDER BY s.time \r\n" + 
				"";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				m = new MsgStatistical();
				m.setCount(rs.getString("countSession"));
				m.setTime(rs.getString("time"));
				list.add(m);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 根据场厅获取场次时间与电影时长
	 */
	public MsgStatistical selectByHallId(String where) {
		
		MsgStatistical m = null;
		
		String sql = "SELECT\r\n" + 
				"	s.time AS showTime,\r\n" + 
				"	m.duration AS duration\r\n" + 
				"FROM\r\n" + 
				"	SESSION s\r\n" + 
				"LEFT JOIN movie AS m ON s.movieId = m.movieId\r\n" + 
				"where 1=1 "; // where字句
		if(where!=null && !where.trim().equals("")) {
			sql+="and "+ where + "GROUP BY s.time desc limit 1";
		}
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con
					.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				m = new MsgStatistical();
				// 存电影时长
				m.setCount(rs.getString("duration"));
				// 存取场次放映时间
				m.setTime(rs.getString("showTime"));
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return m;
	}
}
